﻿using H2.NET.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using H2.NET.Web.Workflow.DwzCode;
using H2.NET.Web.BI.Model;
using System.Data;
using H2.NET.DBUtility;

namespace H2.NET.Web.BI.Report
{
    public partial class Exec : BasePage
    {
        public String sql = "";
        public String where = "";
        public String widths = "";
        public String ta = "";
        public String tempSql = "";
        public String totalSql = "";
        public int PageSize = 20;
        public int PageNumber = 1;
        public int totalCount = 0;
        public Dictionary<string, string> searchVal = new Dictionary<string, string>();
        public Dictionary<string, string> searchType = new Dictionary<string, string>();
        public List<bi_Column> searchList = new List<bi_Column>();
        //public int isSum = 0;
        public DataTable result = new DataTable();
        public DataTable result1 = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            var context = new UltimusMDC3Entities(); 
            if (AksRequest.GetQueryString("action") == "query")
            {
                string sql = AksRequest.GetQueryString("sql");
                string tsql = AksRequest.GetQueryString("totalSql");
                where = AksRequest.GetQueryString("where");
                //if (!string.IsNullOrEmpty(AksRequest.GetQueryString("isSum")))
                //{
                //    isSum = int.Parse(AksRequest.GetQueryString("isSum"));
                //}
                string searchStr = AksRequest.GetQueryString("search");
                string val = AksRequest.GetQueryString("vals");
                string type = AksRequest.GetQueryString("types");
                string ps = AksRequest.GetQueryString("pageSize");
                string pn = AksRequest.GetQueryString("PageNumber");
                widths = AksRequest.GetQueryString("widths");
                ta = AksRequest.GetQueryString("ta");
                if (!string.IsNullOrEmpty(ps))
                {
                    PageSize = int.Parse(ps);
                }
                if (!string.IsNullOrEmpty(pn))
                {
                    PageNumber = int.Parse(pn);
                }
                if (!string.IsNullOrEmpty(val))
                {
                    List<string> vals = val.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    foreach(var v in vals)
                    {
                        searchVal.Add(v.Substring(0,v.IndexOf("=")),v.Substring(v.IndexOf("=")+1));
                    }
                }
                if (!string.IsNullOrEmpty(type))
                {
                    List<string> types = type.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    foreach(var t in types)
                    {
                        searchType.Add(t.Substring(0,t.IndexOf("=")),t.Substring(t.IndexOf("=")+1));
                    }
                }
                tempSql = sql;
                totalSql = tsql;
                if (!string.IsNullOrEmpty(searchStr))
                {
                    List<string> searchs = searchStr.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    if (searchs.Count > 0)
                    {
                        searchList = context.bi_Column.Where(c => searchs.Contains(c.bi_Table.bi_DB.name + ".dbo." + c.bi_Table.table_name+"."+c.column_name)).ToList();
                    }
                }
                if (!string.IsNullOrEmpty(where))
                {
                    sql = sql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                    tsql = tsql.Replace(" where", " " + where + " and ").Replace(" nowhere", " " + where);
                }
                else
                {
                    sql = sql.Replace("nowhere", "");
                    tsql = tsql.Replace("nowhere", "");
                }
                string pageFooter = ") tt where tt.Row between " + ((PageNumber - 1) * PageSize+1) + " and "+ (PageNumber * PageSize);
                if (sql.IndexOf("union") >= 0)
                {
                    sql = sql.Replace("union", pageFooter + " union ");
                    result = DbHelperSQL.Query(sql.Substring(0, sql.IndexOf("union"))).Tables[0];
                    result1 = DbHelperSQL.Query(sql.Substring(sql.IndexOf("union") + 5)).Tables[0];
                    //result = AppendDataTable(tempData1, tempData2);
                }
                else
                {
                    sql = sql + pageFooter;
                    result = DbHelperSQL.Query(sql).Tables[0];
                }
                DataTable totalResult = DbHelperSQL.Query(tsql).Tables[0];
                if (totalResult != null)
                {
                    totalCount = totalResult.Rows.Count;
                }
            }
            else
            {
                String tbls = AksRequest.GetQueryString("tbls");
                String rst = AksRequest.GetQueryString("rst");
                String cnRst = AksRequest.GetQueryString("cnRst");
                String sum = AksRequest.GetQueryString("sum");
                String count = AksRequest.GetQueryString("count");
                String avg = AksRequest.GetQueryString("avg");
                String search = AksRequest.GetQueryString("search");
                String dwhere = AksRequest.GetQueryString("where");
                String sort = AksRequest.GetQueryString("sort").Replace("nullSort,", "").Replace(",nullSort", "").Replace("nullSort", "");
                widths = AksRequest.GetQueryString("widths");
                ta = AksRequest.GetQueryString("ta");
                if (!String.IsNullOrEmpty(tbls) && !String.IsNullOrEmpty(rst))
                {
                    tbls = tbls.Substring(0, tbls.Length - 1);
                    List<String> tblList = tbls.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    String join = "";
                    if (tblList.Count() > 1)
                    {
                        foreach (var tb in tblList)
                        {
                            var ft = context.bi_Table.Where(t => t.bi_DB.name + ".dbo." + t.table_name == tb).FirstOrDefault();
                            //拼接join条件
                            if (ft != null)
                            {
                                bi_Relation relation = context.bi_Relation.Where(t => (t.bi_Table.id == ft.id && tblList.Contains(t.bi_Table1.bi_DB.name + ".dbo." + t.bi_Table1.table_name)) || (t.bi_Table1.id == ft.id && tblList.Contains(t.bi_Table.bi_DB.name + ".dbo." + t.bi_Table.table_name))).FirstOrDefault();
                                if (relation != null)
                                {
                                    String tableJoin = relation.bi_Table.bi_DB.name + ".dbo." + relation.bi_Table.table_name + "." + relation.bi_Column.column_name + "=" + relation.bi_Table1.bi_DB.name + ".dbo." + relation.bi_Table1.table_name + "." + relation.bi_Column1.column_name;
                                    if (join.IndexOf(tableJoin) < 0)
                                    {
                                        join += (String.IsNullOrEmpty(join) ? " where " : " and ") + tableJoin;
                                    }
                                }
                            }
                        }
                    }
                    if (!string.IsNullOrEmpty(dwhere))
                    {
                        join += (String.IsNullOrEmpty(join) ? " where " : " and ") + dwhere;
                    }
                    string pageHead = "select " + cnRst + " from(";
                    string pageFooter = ") tt where tt.Row between " + ((PageNumber - 1) * PageSize+1) + " and "+ (PageNumber * PageSize);
                    string pageRow = " ROW_NUMBER() OVER ";
                    string order = "";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        order = " order by " + sort;
                    }
                    string pageOver = "(" + order + ") as Row,";
                    sql = pageHead + "select " + pageRow + pageOver + rst + " from " + tbls + join + pageFooter;
                    tempSql = pageHead + "select " + pageRow + pageOver + rst + " from " + tbls + (String.IsNullOrEmpty(join) ? " nowhere " : join);
                    totalSql = "select " + rst + " from " + tbls + (String.IsNullOrEmpty(join) ? " nowhere " : join);
                    //拼接合计
                    List<String> sumList = new List<string>();
                    List<String> countList = new List<string>();
                    List<String> avgList = new List<string>();
                    if (!String.IsNullOrEmpty(sum))
                    {
                        sumList = sum.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    }
                    if (!String.IsNullOrEmpty(count))
                    {
                        countList = count.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    }
                    if (!String.IsNullOrEmpty(avg))
                    {
                        avgList = avg.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    }
                    if (sumList.Count()>0 || countList.Count()>0 || avgList.Count()>0)
                    {
                        //isSum = 1;
                        List<String> rstList = rst.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        tempSql += " union ";
                        sql += " union ";
                        string newRst = "";
                        foreach (var rs in rstList)
                        {
                            string temp = rs.Split(' ')[0];
                            string temp1 = rs.Split(' ')[1];
                            if (!String.IsNullOrEmpty(newRst))
                            {
                                newRst += ",";
                            }
                            if (sumList.Contains(temp))
                            {
                                newRst += "sum(" + temp + ") '【"+temp1.Replace("'","")+"】合计统计'";
                            }
                            else if (countList.Contains(temp))
                            {
                                newRst += "count(" + temp + ") '【" + temp1.Replace("'", "") + "】数量统计'";
                            }
                            else if (avgList.Contains(temp))
                            {
                                newRst += "avg(" + temp + ") '【" + temp1.Replace("'", "") + "】平均统计'";
                            }
                            else
                            {
                                newRst += "null " + temp1;
                            }
                        }
                        tempSql += "select " + newRst + " from " + tbls + (String.IsNullOrEmpty(join) ? " nowhere " : join);
                        sql += "select " + newRst + " from " + tbls + join;                 
                    }
                    where = join;

                    if (sql.IndexOf("union") >= 0)
                    {
                        result = DbHelperSQL.Query(sql.Substring(0, sql.IndexOf("union"))).Tables[0];
                        result1 = DbHelperSQL.Query(sql.Substring(sql.IndexOf("union") + 5)).Tables[0];
                        //result = AppendDataTable(tempData1, tempData2);
                    }
                    else
                    {
                        result = DbHelperSQL.Query(sql).Tables[0];
                    }
                    if (!string.IsNullOrEmpty(search))
                    {
                        var sh = search.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        searchList = context.bi_Column.Where(c => sh.Contains(c.bi_Table.bi_DB.name + ".dbo." + c.bi_Table.table_name + "." + c.column_name)).ToList();
                    }
                    DataTable totalResult = DbHelperSQL.Query(totalSql.Replace("nowhere","")).Tables[0];
                    if(totalResult!=null){
                        totalCount = totalResult.Rows.Count;
                    }
                }
                else
                {
                    ErrorMsg("对不起，该页无法刷新，请重新查询或检索数据！");
                    return;
                }
            }
            
        }
        public static DataTable AppendDataTable(DataTable hostDt, DataTable clientDt)
        {
            if (hostDt != null && hostDt.Rows.Count>0)
            {
                object[] obj = new object[hostDt.Columns.Count];
                for (int i = 0; i < clientDt.Rows.Count; i++)
                {
                    clientDt.Rows[i].ItemArray.CopyTo(obj, 0);
                    hostDt.Rows.Add(obj);
                }
            }
            return hostDt;
        }
    }
}